package services;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JOptionPane;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoadPBTemplate implements Runnable{

	//Template
	private Integer[] sMON = {Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, 
			Calendar.THURSDAY, Calendar.FRIDAY, Calendar.SATURDAY, Calendar.SUNDAY };
	private Integer[] sTUE = {Calendar.TUESDAY, Calendar.WEDNESDAY, Calendar.THURSDAY, 
			Calendar.FRIDAY, Calendar.SATURDAY, Calendar.SUNDAY,Calendar.MONDAY};
	private Integer[] sWED = {Calendar.WEDNESDAY, Calendar.THURSDAY, Calendar.FRIDAY, 
			Calendar.SATURDAY, Calendar.SUNDAY, Calendar.MONDAY, Calendar.TUESDAY };
	private Integer[] sTHU = {Calendar.THURSDAY, Calendar.FRIDAY, Calendar.SATURDAY, Calendar.SUNDAY,
			Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY};
	private Integer[] sFRI = {Calendar.FRIDAY, Calendar.SATURDAY, Calendar.SUNDAY,
			Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, 
			Calendar.THURSDAY};
	private Integer[] sSAT = {Calendar.SATURDAY, Calendar.SUNDAY, Calendar.MONDAY, Calendar.TUESDAY, Calendar.WEDNESDAY, 
			Calendar.THURSDAY, Calendar.FRIDAY};
	private Integer[] sSUN = {Calendar.SUNDAY, Calendar.MONDAY ,Calendar.TUESDAY, Calendar.WEDNESDAY, 
			Calendar.THURSDAY, Calendar.FRIDAY, Calendar.SATURDAY };
	
	private String WorkBookName;
	//Output file name
	private String sPBFileName;
	//true: write, false read
	private boolean doLoading;
	public boolean isLoadSuccess;
	private FileInputStream fisReader;
	private FileOutputStream fosWriter;
	private XSSFWorkbook wb;
	//PODs, row
	private Map<String, Integer> mPodStartRow;
	//PODs, col
	private Map<String, Integer> mPodStartCol;
	//PODs, sheet
	private Map<String, String> mPodOnSheet;
	//PODs, offset
	private Map<String, Integer> mPodWeekdays;
	//PODs, capacity
	private Map<String, Integer> mPodCap;
	//PODs, max day
	private Map<String, Integer> mPodMaxDay;
	
	private Map<Integer, Integer[]> WeekDays;
	private int iPodWidth, iPodHeight;
	
	public LoadPBTemplate(){
		doLoading = false;
		isLoadSuccess = false;
		iPodWidth = 0;
		iPodHeight = 0;
		WorkBookName = "";
		sPBFileName = "";
		fisReader = null;
		fosWriter = null;
		wb = null;
		mPodStartRow = new HashMap<String, Integer>();
		mPodStartCol = new HashMap<String, Integer>();
		mPodCap = new HashMap<String, Integer>();
		mPodWeekdays = new HashMap<String, Integer>();
		mPodOnSheet = new HashMap<String, String>();
		mPodMaxDay = new HashMap<String, Integer>();
		WeekDays = new HashMap<Integer, Integer[]>();
		WeekDays.put(0, sMON);
		WeekDays.put(1, sTUE);
		WeekDays.put(2, sWED);
		WeekDays.put(3, sTHU);
		WeekDays.put(4, sFRI);
		WeekDays.put(5, sSAT);
		WeekDays.put(6, sSUN);
		
	}
	public void setBookingSheetName(String outputFile){
		sPBFileName = outputFile;
	}
	
	public void setWorkBookName(String templateFile){
		WorkBookName = templateFile;
	}
	
	public void setRead(){
		doLoading = true;
	}
	
	public void setWrite(){
		doLoading = false;
	}
	
	private void loadPBSettings()
	{
		XSSFSheet sSheet;
		XSSFRow rRow;
		XSSFCell cCell;
		String sPodName = "";
		
		Pattern pCoordinate;
		Matcher m;
		try {
			fisReader = new FileInputStream(WorkBookName);
			wb = new XSSFWorkbook(fisReader);
			sSheet = wb.getSheet(".");

			for (int i=1; i<sSheet.getPhysicalNumberOfRows(); i++){
				rRow = sSheet.getRow(i);
				for (int j=0; j<rRow.getPhysicalNumberOfCells(); j++){
					cCell = rRow.getCell(j);		
					switch (j){
					case 0:
						//let pod id identifier case insensitive
						sPodName = cCell.getStringCellValue().toUpperCase();
						break;
					case 1:
						mPodOnSheet.put(sPodName, cCell.getStringCellValue());
						break;
					case 2:
						pCoordinate = Pattern.compile("^r([0-9]+)c([0-9]+)");
						m = pCoordinate.matcher(cCell.getStringCellValue());
						while (m.find()){
							mPodStartRow.put(sPodName, Integer.parseInt(m.group(1)));
							mPodStartCol.put(sPodName, Integer.parseInt(m.group(2)));
						}
						break;
					case 3:
						String sweekdays = "";
						int iweekdays = -1;
						if (cCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
							iweekdays = (int)cCell.getNumericCellValue();
							if (iweekdays<0 && iweekdays>6){
								iweekdays = iweekdays%6;
							}
							mPodWeekdays.put(sPodName, iweekdays);
							break;
						}
						if (cCell.getCellType() == XSSFCell.CELL_TYPE_STRING){
							sweekdays = cCell.getStringCellValue();
							if (!sweekdays.isEmpty()){
								Pattern p = Pattern.compile("([Mm][Oo]){1}.*|" +
															"([Tt][Uu]){1}.*|" +
															"([Ww][Ee]){1}.*|" +
															"([Tt][Hh]){1}.*|" +
															"([Ff][Rr]){1}.*|" +
															"([Ss][Aa]){1}.*|" +
															"([Ss][Uu]){1}.*");
								Matcher m2 = p.matcher(sweekdays);
								if (m2.matches())
								{
									for (int eachGroup=1; eachGroup<m2.groupCount()+1; eachGroup++){
										if (m2.group(eachGroup)!=null){
											mPodWeekdays.put(sPodName, eachGroup-1);
										}
									}
								}
							}
							break;
						}				
						break;
					case 4:
						mPodCap.put(sPodName, (int) cCell.getNumericCellValue());
						break;
					case 5:
						mPodMaxDay.put(sPodName, (int) cCell.getNumericCellValue());
						break;
					}
				}
			}			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fisReader != null){
				try {
					fisReader.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
	
	private boolean loadPB(){
		XSSFSheet sSheet;
		int iRow, iCell;
		Vector<XSSFRow> vRowData = new Vector<XSSFRow>();
		Vector<Vector> vLoadRowData = new Vector();
		
		try {
			fisReader = new FileInputStream(sPBFileName);
			wb = new XSSFWorkbook(fisReader);
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fisReader!=null){
				try {
					fisReader.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		sSheet = wb.getSheet("Check_list");
		if (sSheet == null){
			return false;
		}
		
		iRow = 1;
		if ((sSheet.getPhysicalNumberOfRows()<1) || 
				(sSheet.getRow(iRow) == null) || 
				(sSheet.getRow(iRow).getPhysicalNumberOfCells() < DataCache.sColumnName.length)){
			return false;
		}
		while (iRow<sSheet.getPhysicalNumberOfRows())
		{
			vRowData.add(sSheet.getRow(iRow));
			iRow++;
		}
		//"Country", "Course", "POD", "Student", "Start Date", "Days", "Image", "Instructor"
		//System.out.println(vRowData);
		for (iRow=0; iRow<vRowData.size(); iRow++)
		{
			/*vTmpRow.add(""); //Region
				vTmpRow.add(""); //Course
				vTmpRow.add(""); //Pod
				vTmpRow.add(0); //Student
				vTmpRow.add(DataCache.dToday); //Start date
				vTmpRow.add(5); //Days
				vTmpRow.add("");
				vTmpRow.add("");*/
			vLoadRowData.add(new Vector());
			XSSFRow cRow = vRowData.elementAt(iRow);
			for (iCell=0; iCell<DataCache.sColumnName.length; iCell++){
				XSSFCell cCell = cRow.getCell(iCell);
				switch (iCell){
				case 3:
					if (cCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
						vLoadRowData.elementAt(iRow).add(new Integer((int)cCell.getNumericCellValue()));
					}
					else{
						vLoadRowData.elementAt(iRow).add(new Integer(0));
					}
					break;
				case 4:
					if (cCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
						vLoadRowData.elementAt(iRow).add(cCell.getDateCellValue());
					}
					else{
						vLoadRowData.elementAt(iRow).add(DataCache.dToday);
					}
					break;
				case 5:
					if (cCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
						vLoadRowData.elementAt(iRow).add(new Integer((int)cCell.getNumericCellValue()));
					}
					else{
						vLoadRowData.elementAt(iRow).add(new Integer(0));
					}
					break;
				default:
					if (cCell.getCellType() == XSSFCell.CELL_TYPE_STRING){
						vLoadRowData.elementAt(iRow).add(cCell.getStringCellValue());
					}
					else{
						vLoadRowData.elementAt(iRow).add("<n/a>");
					}
					break;
				}
			}
		}
		if (vLoadRowData.size()>0){
			DataCache.vIP.removeAllElements();
			DataCache.vIP.addAll(vLoadRowData);
			return true;
		}
		return false;
	}
	
	private void savePB()
	{
		XSSFSheet sSheet;
		XSSFRow rRow;
		int iXSSFRow;
		XSSFCell cCell;
		String sPodName = "";
		String sCheckList = "Check_list";
		Date dTmpDate = DataCache.dToday;
		Set<String> allPODs = mPodStartRow.keySet();
		boolean skipped = false;
		
		Pattern p1;
		Matcher m;
		
		if (!DataCache.copyFile(WorkBookName, sPBFileName)){
			return ;
		}
		
		try {
			fisReader = new FileInputStream(sPBFileName);
			wb = new XSSFWorkbook(fisReader);				
		} catch (FileNotFoundException e) {
		} catch (IOException e) {
		} finally {
			if (fisReader != null){
				try {
					fisReader.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		
		sSheet = wb.getSheet(sCheckList);
		if (sSheet == null){
			sSheet = wb.createSheet(sCheckList);
		}
		rRow = sSheet.createRow(0);
		for (int iCol=0; iCol<DataCache.sColumnName.length; iCol++)
		{
			XSSFCell xssfColNamCel = rRow.createCell(iCol);
			xssfColNamCel.setCellValue(DataCache.sColumnName[iCol]);
		}
		
		iXSSFRow = 1;
		/*public static String sColumnName[] = {"Country", "Course", "POD", "Student", "Start Date", "Days", "Image", "Instructor"};*/
		for (int ivIProw=0; ivIProw<DataCache.vIP.size(); ivIProw ++)
		{
			Vector vTmpRow = (Vector) DataCache.vIP.elementAt(ivIProw);
			String sCellValue = "";
			
			rRow = sSheet.createRow(iXSSFRow + ivIProw);
			int iCurrentRow = rRow.getRowNum()+1;
			XSSFCellStyle csColor = wb.createCellStyle();
			XSSFColor rowColor = new XSSFColor(Color.lightGray);
			rowColor.setTint(0.7);
			csColor.setFillForegroundColor(rowColor);
			csColor.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			XSSFCreationHelper dateFormat = wb.getCreationHelper();
			XSSFCellStyle dateCellStyle = wb.createCellStyle();
			dateCellStyle.setDataFormat(
					dateFormat.createDataFormat().getFormat("m/d"));
			dateCellStyle.setFillForegroundColor(rowColor);
			dateCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			//Region
			cCell = rRow.createCell(0);
			cCell.setCellValue(vTmpRow.elementAt(0).toString());
			cCell.setCellStyle(csColor);
			
			//Course name
			cCell = rRow.createCell(1);
			cCell.setCellValue(vTmpRow.elementAt(1).toString());
			cCell.setCellStyle(csColor);
			
			//POD
			cCell = rRow.createCell(2);
			String sPodId = vTmpRow.elementAt(2).toString();
			sPodId = sPodId.trim().toUpperCase();
			//sPodId = sPodId.replaceAll(" ", "");
			System.out.println("Handle: " + sPodId);
			//^(POD|[A-Z]{3})(.*|-)([0-9]+)
			
			if (sPodId.matches("^POD.*"))
			{
				p1 = Pattern.compile("([0-9]+)$");
				m = p1.matcher(sPodId);
				while (m.find())
				{
					if(m.group(1).length() == 1){
						sPodId = "POD0" + m.group(1);
					}
					else {
						sPodId = "POD" + m.group(1);
					}
				} 
				System.out.println("Through POD check: " + sPodId);
			}
			else
			{
				p1 = Pattern.compile("^([A-Z]{3}).*(ESX).*([0-9]+)$", Pattern.CASE_INSENSITIVE);
				m = p1.matcher(sPodId);
				while (m.find())
				{
					sPodId = m.group(1).toUpperCase() + "-" + m.group(2).toUpperCase() + m.group(3);
				}
				System.out.println("Through ESX check: " + sPodId);
			}
			Iterator<String> it = allPODs.iterator();
			while (it.hasNext())
			{
				String sTempit = it.next();
				if (sTempit.equalsIgnoreCase(sPodId)){
					System.out.println("Found: " + sPodId);
					skipped = false;
					break;
				}
				skipped = true;
			}
			
			if (vTmpRow.elementAt(2) != null)
			{
				cCell.setCellValue(sPodId);
			}
			cCell.setCellStyle(csColor);
						
			//Student (Pod width)
			cCell = rRow.createCell(3);
			iPodWidth = Integer.parseInt(vTmpRow.elementAt(3).toString());
			cCell.setCellValue(iPodWidth);
			cCell.setCellStyle(csColor);
			
			//Start date (Start coordinate)
			dTmpDate = (Date) vTmpRow.elementAt(4);
			System.out.println(dTmpDate);
			
			Calendar calTmp = Calendar.getInstance();
			calTmp.setTime(dTmpDate);
			
			cCell = rRow.createCell(4);	
			cCell.setCellValue(dTmpDate);
			cCell.setCellStyle(dateCellStyle);
			
			//Days (pod height)
			cCell = rRow.createCell(5);
			iPodHeight = Integer.parseInt(vTmpRow.elementAt(5).toString());
			cCell.setCellValue(iPodHeight);
			cCell.setCellStyle(csColor);
			
			//Image
			cCell = rRow.createCell(6);
			cCell.setCellValue(vTmpRow.elementAt(6).toString());
			cCell.setCellStyle(csColor);
			
			//Instructor
			cCell = rRow.createCell(7);
			cCell.setCellValue(vTmpRow.elementAt(7).toString());
			cCell.setCellStyle(csColor);
			
			//Booking formula
			cCell = rRow.createCell(8);
			//CONCATENATE(A2;" ";B2;" ";"(";D2;")";" ";"(";C2;")";" ";H2;" ";G2)
			cCell.setCellFormula("CONCATENATE(A"+ iCurrentRow +
					",\" \",B" + iCurrentRow +
					",\" \",\"(\",D" + iCurrentRow +
					",\")\",\" \",\"(\",C" + iCurrentRow + 
					",\")\",\" \",H" + iCurrentRow + 
					",\" \",G" + iCurrentRow +
					")");
			cCell.setCellStyle(csColor);
			
			if (!skipped){
				if (iPodWidth > mPodCap.get(sPodId)){
					iPodWidth = mPodCap.get(sPodId);
				}
				
				if (iPodHeight > mPodMaxDay.get(sPodId)){
					iPodHeight = mPodMaxDay.get(sPodId);
				}
				
				if (!sPodId.isEmpty()){
					System.out.println("Start Row: "+mPodStartRow.get(sPodId));
					System.out.println("Start offset: " + calTmp.getFirstDayOfWeek()%7); // 
					System.out.println("Start Col: " + mPodStartCol.get(sPodId));
					System.out.println("w: " + iPodWidth);
					System.out.println("h: " + iPodHeight);
					//'Check_list'!I2
					System.out.println("\'"+sCheckList+"\'!I"+iCurrentRow);
				int offset;
				for (offset=0; offset<WeekDays.get(mPodWeekdays.get(sPodId)).length; offset++){
					if (calTmp.get(Calendar.DAY_OF_WEEK) == WeekDays.get(mPodWeekdays.get(sPodId))[offset]){
						break;
					}
				}
				if (offset+iPodHeight<=mPodMaxDay.get(sPodId)){
					makePodBooking(wb, sPodId,
							mPodStartRow.get(sPodId) + offset, // 
							mPodStartCol.get(sPodId),
							iPodWidth,
							iPodHeight,
							//'Check_list'!I2
							"\'"+sCheckList+"\'!I"+iCurrentRow);
				}
				else {
					
				}
					int iTempValue = mPodStartCol.get(sPodId) + iPodWidth;
					mPodStartCol.put(sPodId, iTempValue);
					mPodCap.put(sPodId, mPodCap.get(sPodId) - iPodWidth);
				}
				
			}
			/*end of for (int ivIProw=0; ivIProw<DataCache.vIP.size(); ivIProw ++) loop*/
		}
		
		
		try {
			fosWriter = new FileOutputStream(sPBFileName);
			wb.write(fosWriter);
			fosWriter.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fosWriter != null){
				try {
					fosWriter.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}		
	}
	//pass initial cell (iSRow, iSCol) and width height
	private void makePodBooking(XSSFWorkbook xssfwb, String sPodId, 
			int iSRow, int iSCol, int iWidth, int iHeight, 
			String sValue){
		if (iSRow > 0){iSRow = iSRow - 1;}
		if (iSCol > 0){iSCol = iSCol - 1;}
		if (iWidth > 0){iWidth = iWidth + iSCol - 1;}
		if (iHeight > 0){iHeight = iHeight + iSRow - 1;}
		
		Random randomRGB = new Random(System.currentTimeMillis());
		Color filledColor = new Color(randomRGB.nextInt(255),randomRGB.nextInt(255),randomRGB.nextInt(255));
		
		XSSFFont podIdFont = xssfwb.createFont();
		podIdFont.setFontName("Arial");
		podIdFont.setColor(new XSSFColor(new Color(255-filledColor.getRed(),
				255-filledColor.getGreen(),
				255-filledColor.getBlue())));		
		
		XSSFCellStyle monBCStyle = xssfwb.createCellStyle();
		monBCStyle.setFillForegroundColor(new XSSFColor(filledColor));
		monBCStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		monBCStyle.setFont(podIdFont);
		monBCStyle.setWrapText(true);
		monBCStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		monBCStyle.setAlignment(CellStyle.ALIGN_CENTER);
		
		XSSFSheet sSheet = wb.getSheet(mPodOnSheet.get(sPodId));
		if (sSheet == null){
			sSheet = wb.createSheet(mPodOnSheet.get(sPodId));
		}
		XSSFRow rRow = sSheet.getRow(iSRow);
		if (rRow == null){
			rRow = sSheet.createRow(iSRow);
		}
		XSSFCell cCell = rRow.getCell(iSCol);
		if (cCell == null){
			cCell = rRow.createCell(iSCol);
		}
		cCell.setCellFormula(sValue);
		cCell.setCellStyle(monBCStyle);
		sSheet.addMergedRegion(new CellRangeAddress(iSRow, // first row
				iHeight, // last row
				iSCol, // first column
				iWidth));
	}
	
	@Override
	public void run() {
		// TODO Auto-generated method stub
		if (!doLoading){
			if (!WorkBookName.isEmpty())
			{
				loadPBSettings();
				if (!sPBFileName.isEmpty())
				{
					System.out.println("save" + sPBFileName);
					savePB();
				}
			}
		}
		else{
			if (!sPBFileName.isEmpty()){
				System.out.println("load" + sPBFileName);
				isLoadSuccess = loadPB();
			}
		}
	}

}
